-- =============================================
-- This stored procedure cares a unique URLSafe name (Content.URLSafeName field)
-- for a specified Content record.
--
-- Modifications
-- 08/22/2003   E.Tatsui
-- =============================================

CREATE          PROCEDURE amsp_CMGetUniqueContentName
  @InContentID numeric = NULL,
  @InContentName varchar(255) = NULL,
  @InNavMenuID numeric,
  @OutURLSafeName varchar(255) OUTPUT
AS
BEGIN

  IF @InContentID IS NULL AND @InContentName IS NULL BEGIN
    RAISERROR('Either ContentID or Title is required as input parameter',16,1)
    RETURN
  END

  DECLARE
    @Name varchar(255),
    @URLSafeName varchar(255),
    @RowNum integer,
    @Counter integer,
    @DefaultContentTitleLength numeric

  IF @InContentName IS NOT NULL
    SET @Name = @InContentName
  ELSE
    SELECT @Name = Name
      FROM Content
     WHERE ContentID = @InContentID

  SELECT @DefaultContentTitleLength = CAST(Value as numeric)
    FROM System_Variable
   WHERE Name = 'CMDefaultContentTitleLength'

  -- Remove special characters that shouldn't be used in URL.
  SET @URLSafeName = REPLACE(@Name, ' ', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '.', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '?', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '\', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '/', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '*', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, ':', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '|', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '<', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '&', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '''', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '#', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '>', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '>', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '>', '_')
  SET @URLSafeName = REPLACE(@URLSafeName, '@', '_')

  -- Multiple underscores looks so untidy.
  WHILE CHARINDEX('__', @URLSafeName) > 0 BEGIN
    SET @URLSafeName = REPLACE(@URLSafeName, '__','_')
  END
  
  SET @URLSafeName = Left(@URLSafeName,@DefaultContentTitleLength -1)

  -- It needs to be unique.
  SELECT @RowNum = COUNT(*)
    FROM Content
   WHERE UPPER(URLSafeName) = UPPER(@URLSafeName)
     AND NavMenuID = @InNavMenuID

  -- If we find a record with the same directory name, let's append a number at the end.    
  SET @Counter = 0
  WHILE @RowNum > 0 BEGIN
    SET @Counter = @Counter + 1
    SELECT @RowNum = COUNT(*)
      FROM Content
     WHERE UPPER(URLSafeName) = UPPER(@URLSafeName) + Cast(@Counter as varchar(255))
       AND NavMenuID = @InNavMenuID
  END    
  
  IF @Counter > 0
    SET @URLSafeName = @URLSafeName + Cast(@Counter as varchar(255))

  SET @OutURLSafeName = @URLSafeName
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetUniqueContentName] TO [IMIS]
GO